CREATE TABLE [dbo].[UserMain]
(
[UserKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_UserMain_UserKey] DEFAULT (newid()),
[ContactMaster] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_UserMain_ContactMaster] DEFAULT (''),
[UserId] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_UserMain_UserId] DEFAULT (''),
[IsDisabled] [bit] NOT NULL CONSTRAINT [DF_UserMain_IsDisabled] DEFAULT ((1)),
[EffectiveDate] [datetime] NOT NULL,
[ExpirationDate] [datetime] NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_UserMain_UpdatedOn] DEFAULT (getdate()),
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[MarkedForDeleteOn] [datetime] NULL,
[DefaultDepartmentGroupKey] [uniqueidentifier] NULL,
[DefaultPerspectiveKey] [uniqueidentifier] NULL,
[ProviderKey] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_UserMain_Delete]
   ON [dbo].[UserMain]
   FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @groupTypeKey uniqueidentifier
    SET @groupTypeKey = '636A1E55-38BC-4A8F-AC8D-A6873D697F18'
    DELETE gm
      FROM deleted d INNER JOIN [dbo].[ContactMain] cm ON d.[UserKey] = cm.[ContactKey]
                     INNER JOIN [dbo].[Name] n ON cm.[SyncContactID] = n.[ID]
                     INNER JOIN [dbo].[Member_Types] mt ON mt.[MEMBER_TYPE] = n.[MEMBER_TYPE]
                     INNER JOIN [dbo].[GroupMain] g ON g.[Name] = mt.[DESCRIPTION] AND g.[GroupTypeKey] = @groupTypeKey
                     INNER JOIN [dbo].[GroupMember] gm ON gm.[GroupKey] = g.[GroupKey] AND gm.[MemberContactKey] = cm.[ContactKey]

    SET NOCOUNT OFF
END


GO
CREATE TRIGGER [dbo].[asi_UserMain_Insert]
   ON [dbo].[UserMain]
   FOR INSERT
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @now datetime
    DECLARE @userKey uniqueidentifier
    DECLARE @groupTypeKey uniqueidentifier
    
    SET @groupTypeKey = '636A1E55-38BC-4A8F-AC8D-A6873D697F18'
    SELECT @now = GETDATE()
    SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'SYSTEM'
    IF @userKey IS NULL SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'MANAGER'

    INSERT INTO [dbo].[GroupMember] ([GroupMemberKey], [GroupKey], [MemberContactKey], [IsActive],
                                     [CreatedByUserKey], [CreatedOn], [UpdatedByUserKey], [UpdatedOn],
                                     [DropDate], [JoinDate], [MarkedForDeleteOn])
        SELECT NEWID(), g.GroupKey, i.UserKey, 1, @userKey, @now, @userKey, @now, NULL, NULL, NULL  
          FROM inserted i INNER JOIN [dbo].[ContactMain] cm ON i.[UserKey] = cm.[ContactKey]
                          INNER JOIN [dbo].[Name] n ON cm.[SyncContactID] = n.[ID]
                          INNER JOIN [dbo].[Member_Types] mt ON mt.[MEMBER_TYPE] = n.[MEMBER_TYPE]
                          INNER JOIN [dbo].[GroupMain] g ON g.[Name] = mt.[DESCRIPTION] AND g.[GroupTypeKey] = @groupTypeKey
           AND NOT EXISTS (SELECT 1 FROM [dbo].[GroupMember] gm WHERE gm.GroupKey = g.GroupKey)

    SET NOCOUNT OFF
END

GO
CREATE TRIGGER [dbo].[asi_UserMain_Update]
   ON [dbo].[UserMain]
   FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE @groupTypeKey uniqueidentifier
    SET @groupTypeKey = '636A1E55-38BC-4A8F-AC8D-A6873D697F18'
    
    -- Delete MemberType security record if User is being disabled
    IF EXISTS (SELECT 1 FROM inserted i INNER JOIN deleted d ON i.[UserKey] = d.[UserKey] WHERE i.[IsDisabled] = 1 AND d.[IsDisabled] = 0)
    BEGIN
        DELETE gm
          FROM deleted d INNER JOIN inserted i ON d.[UserKey] = i.[UserKey]
                         INNER JOIN [dbo].[ContactMain] cm ON i.[UserKey] = cm.[ContactKey]
                         INNER JOIN [dbo].[Name] n ON cm.[SyncContactID] = n.[ID]
                         INNER JOIN [dbo].[Member_Types] mt ON mt.[MEMBER_TYPE] = n.[MEMBER_TYPE]
                         INNER JOIN [dbo].[GroupMain] g ON g.[Name] = mt.[DESCRIPTION] AND g.[GroupTypeKey] = @groupTypeKey
                         INNER JOIN [dbo].[GroupMember] gm ON gm.[GroupKey] = g.[GroupKey] AND gm.[MemberContactKey] = cm.[ContactKey]
         WHERE i.[IsDisabled] = 1 AND d.[IsDisabled] = 0
    END
    
    -- Insert MemberType security record if User is being enabled
    IF EXISTS (SELECT 1 FROM inserted i INNER JOIN deleted d ON i.[UserKey] = d.[UserKey] WHERE i.[IsDisabled] = 0 AND d.[IsDisabled] = 1)
    BEGIN
        DECLARE @now datetime
        DECLARE @userKey uniqueidentifier
        SELECT @now = GETDATE()
        SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'SYSTEM'
        IF @userKey IS NULL SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'MANAGER'

        INSERT INTO [dbo].[GroupMember] ([GroupMemberKey], [GroupKey], [MemberContactKey], [IsActive],
                                         [CreatedByUserKey], [CreatedOn], [UpdatedByUserKey], [UpdatedOn],
                                         [DropDate], [JoinDate], [MarkedForDeleteOn])
            SELECT NEWID(), g.GroupKey, i.UserKey, 1, @userKey, @now, @userKey, @now, NULL, NULL, NULL  
              FROM inserted i INNER JOIN deleted d ON i.[UserKey] = d.[UserKey]
                              INNER JOIN [dbo].[ContactMain] cm ON i.[UserKey] = cm.[ContactKey]
                              INNER JOIN [dbo].[Name] n ON cm.[SyncContactID] = n.[ID]
                              INNER JOIN [dbo].[Member_Types] mt ON mt.[MEMBER_TYPE] = n.[MEMBER_TYPE]
                              INNER JOIN [dbo].[GroupMain] g ON g.[Name] = mt.[DESCRIPTION] AND g.[GroupTypeKey] = @groupTypeKey
             WHERE i.[IsDisabled] = 0 AND d.[IsDisabled] = 1
               AND NOT EXISTS (SELECT 1 FROM [dbo].[GroupMember] gm WHERE gm.GroupKey = g.GroupKey AND gm.MemberContactKey = cm.ContactKey)
    END
    
    SET NOCOUNT OFF
END

GO
ALTER TABLE [dbo].[UserMain] ADD CONSTRAINT [PK_UserMain] PRIMARY KEY CLUSTERED ([UserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ContactMaster] ON [dbo].[UserMain] ([ContactMaster]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserMain_CreatedByUserKey] ON [dbo].[UserMain] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserMain_DefaultDepartmentGroupKey] ON [dbo].[UserMain] ([DefaultDepartmentGroupKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserMain_DefaultPerspectiveKey] ON [dbo].[UserMain] ([DefaultPerspectiveKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserMain_UserId] ON [dbo].[UserMain] ([UserId]) INCLUDE ([ProviderKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserMain_UpdatedByUserKey] ON [dbo].[UserMain] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserMain] ADD CONSTRAINT [FK_UserMain_ContactMain] FOREIGN KEY ([UserKey]) REFERENCES [dbo].[ContactMain] ([ContactKey])
GO
ALTER TABLE [dbo].[UserMain] ADD CONSTRAINT [FK_UserMain_GroupMain] FOREIGN KEY ([DefaultDepartmentGroupKey]) REFERENCES [dbo].[GroupMain] ([GroupKey])
GO
ALTER TABLE [dbo].[UserMain] ADD CONSTRAINT [FK_UserMain_Perspective] FOREIGN KEY ([DefaultPerspectiveKey]) REFERENCES [dbo].[Perspective] ([PerspectiveKey])
GO
ALTER TABLE [dbo].[UserMain] ADD CONSTRAINT [FK_UserMain_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[UserMain] ADD CONSTRAINT [FK_UserMain_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
GRANT SELECT ON  [dbo].[UserMain] TO [IMIS]
GO